﻿namespace Busconeitor.DataLayer
{
    using Domain.Entities;
    using System;
    using System.Data.SqlClient;
    using System.Text;
    using Domain.Utils.HashTable;
    using log4net;
    using Domain.Utils.SimpleList;
    using System.Data;

    public class DocumentDb
    {
        #region Atributos

        //Maximo numero de documentos para las sugerencias.
        private const int MaxWordsWithoutIndexRadius = 5;
        //SQL para obtener un documento especifico
        private const string GetDocument = "SELECT idDocument, name, location, pagesCount, maxTF, size FROM DOCUMENT WHERE idDocument = ?";
        //Obtengo la cantiad de documentos de la BD
        private const string CountDocuments = "SELECT COUNT(*) AS total FROM DOCUMENT ";
        //STORE PROCEDURE para insertar doc
        private const string InsertDocument = "DOCUMENT_Insert";
        //Ejecuto esta linea al finalizar el parseo de todos los documentos para obtener el nr de cada termino
        private const string UpdateTermNr = "UPDATE TERM SET NR = (SELECT COUNT(p.idDocument) FROM POST p  WHERE p.idTerm = Term.idTerm GROUP BY p.idTerm)";
        //Ejecuto esta linea al finalizar el parseo de todos los documentos para obtener el maxTf de cada termino    
        private const string UpdateMaxtf = "UPDATE TERM SET maxTf = (SELECT MAX(p.tf) FROM POST p WHERE p.idTerm = Term.idTerm GROUP BY p.idTerm)";
        //Obtiene toda la informacion de la lista de posteos para los terminos de busqueda
        private const string GetPostQuery = "SELECT p.idDocument, p.idTerm, p.tf, t.maxTf FROM POST p INNER JOIN TERM t ON t.idTerm = p.idTerm WHERE p.idTerm IN (?)";
        //Obtiene las palabras que nos van a permitir construir el fragmento segun los terminos buscados
        private const string DocumentWords = "SELECT TOP(@p0) DT.term, I.[index] FROM [INDEX] I INNER JOIN DIRTYTERM DT ON I.idDirtyTerm = DT.idDirtyTerm WHERE I.[index] >= @p1 AND I.idDocument = @p2 ORDER BY I.[index] ASC";
        //Obtiene el indice el menor indice del termino buscado, esto sirve para la construccion del fragmento
        private const string GetdocumentsMinIndex = "SELECT I.idDocument, MIN(I.[index]) AS indice FROM TERM T INNER JOIN DIRTYTERM DT ON T.idTerm = DT.idTerm INNER JOIN [INDEX] I ON DT.idDirtyTerm=I.idDirtyTerm WHERE I.idDocument IN (i?) AND T.cleanTerm IN (w?) GROUP BY I.idDocument ORDER BY I.idDocument ASC";

        #endregion

        #region Constructores

        #endregion

        #region Propiedades
        #endregion

        #region Metodos

        /// <summary>
        /// Obtiene una lista de documentos en la que se encuentran los terminos de busqueda
        /// </summary>
        /// <param name="pQueryTerms"></param>
        /// <returns>una lista de documentos con los terminos buscados en cada documento</returns>
        public static HashList FindPost(HashList pQueryTerms)
        {
            try
            {
                var listDocument = new HashList(10000);
                TerminoEntity term;
                //TODO: ver por que esta linea esta aqui
                var length = pQueryTerms.Size();
                var queryItems = new StringBuilder();

                pQueryTerms.StartIterator();

                while (pQueryTerms.HasNext())
                {
                    term = (TerminoEntity)pQueryTerms.Next();
                    queryItems.Append(term.Id + ",");
                }

                var query = GetPostQuery.Replace("?", queryItems.ToString().Remove(queryItems.Length - 1, 1));
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                while (result.Read())
                {
                    var idTerm = result.GetInt32(1);
                    var t = new TerminoEntity(idTerm);
                    term = (TerminoEntity)pQueryTerms.Get(t);

                    var idDoc = result.GetInt32(0);                    
                    var document = (DocumentoEntity)listDocument.Get(new DocumentoEntity(idDoc));

                    if (document == null)
                    {
                        document = GetDocumento(idDoc);
                        document.AddTermino(term.Id, result.GetInt32(2), term.Nr, result.GetInt32(3));
                        listDocument.Put(document);
                    }
                    else
                    {
                        document.AddTermino(term.Id, result.GetInt32(2), term.Nr, result.GetInt32(3));
                    }
                }

                result.Close();
                return listDocument;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Obtenemos el documento de acuerdo a su Id.
        /// </summary>
        /// <param name="idDocument"></param>
        /// <returns></returns>
        public static DocumentoEntity GetDocumento(int idDocument)
        {
            try
            {
                DocumentoEntity document = null;
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                // Preparo la consulta SQL con su parametro
                var query = GetDocument.Replace("?", idDocument.ToString());
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                if (result.Read())
                {
                    // Seteo los valores que obtengo de la BD en un objeto Document
                    document = new DocumentoEntity();
                    document.Id = idDocument;
                    document.Name = result.GetString(1);
                    document.Location = result.GetString(2);
                    document.PagesCount = result.GetInt32(3);
                    document.MaxTf = result.GetInt32(4);
                    document.Size = (long)result.GetDecimal(5);
                }

                result.Close();
                return document;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Metodo q se ejecuta al finalizar el parseo y persistencia del docuento en el metodo main para actualizar los nr de todos los terminos
        /// </summary>
        public static void UpdateNr()
        {
            try
            {
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                //Preparo la consulta SQL con su parametro
                var query = UpdateTermNr;
                var myCommand = new SqlCommand(query, connection);
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
            }
        }

        /// <summary>
        /// Metodo q se ejecuta al finalizar el parseo y persistencia del docuento en el metodo main para actualizar los maxtf de todos los terminos.
        /// </summary>
        public static void UpdateMaxTf()
        {
            try
            {
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                //Preparo la consulta SQL con su parametro
                var query = UpdateMaxtf;
                var myCommand = new SqlCommand(query, connection);
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
            }
        }

        /// <summary>
        /// Metodo para obtener la cantidad de documentos q se encuentran en la base de datos.
        /// </summary>
        /// <returns></returns>
        public static int GetCountDocument()
        {
            try
            {
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                //Preparo la consulta SQL con su parametro
                var query = CountDocuments;
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();
                var cantidad = 0;

                if (result.Read())
                {
                    cantidad = result.GetInt32(0);
                }

                result.Close();
                return cantidad;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                return 0;
            }
        }

        /// <summary>
        /// Metodo que se encarga de construir el fragmento del documento.
        /// </summary>
        /// <param name="documento"></param>
        /// <param name="pUseRadiusIndex"></param>
        public static void BuildFragment(DocumentoEntity documento, bool pUseRadiusIndex)
        {
            var connection = DbConnection.GetDbInstance().GetDbConnection();
            //Preparo la consulta SQL con su parametro
            var query = new StringBuilder(DocumentWords);

            try
            {
                var minIndex = documento.MinIndex;

                if (pUseRadiusIndex)
                {
                    minIndex -= DocumentoEntity.IndexOffsetRadius;

                    if (minIndex < 0)
                    {
                        minIndex = 0;
                    }
                }

                if (pUseRadiusIndex)
                {
                    var p = DocumentoEntity.IndexOffsetRadius * 2;
                    query.Replace("@p0", p.ToString());
                }
                else
                {
                    var p = MaxWordsWithoutIndexRadius;
                    query.Replace("@p0", p.ToString());
                }

                query.Replace("@p1", minIndex.ToString());
                query.Replace("@p2", documento.Id.ToString());

                var myCommand = new SqlCommand(query.ToString(), connection);
                var result = myCommand.ExecuteReader();
                var fragment = new StringBuilder(100);

                while (result.Read())
                {
                    fragment.Append(result.GetString(0));
                    fragment.Append(" ");
                }

                documento.Fragment = fragment.ToString().Trim();
                result.Close();
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                throw ex;
            }
        }

        /// <summary>
        /// Metodo que obtiene el indice del termino buscado en el documento que es pasado por parametro que luego nos servira para construir el fragment.
        /// </summary>
        /// <param name="hashListDocuments"></param>
        /// <param name="paginacion"></param>
        /// <param name="pQuery"></param>
        public static void GetDocumentsMinIndex(DocumentoEntity[] hashListDocuments, int paginacion, SimpleList pQuery)
        {
            try
            {
                var queryItems = new StringBuilder();

                foreach (var item in hashListDocuments)
                {
                    queryItems.Append(item.Id + ",");
                }

                var query = GetdocumentsMinIndex.Replace("i?", queryItems.ToString().Remove(queryItems.Length - 1, 1));
                queryItems.Clear();
                pQuery.StartIterator();

                while (pQuery.HasNext())
                {
                    var stringTerm = (string)pQuery.Next();
                    queryItems.Append("'" + stringTerm + "',");
                }

                query = query.Replace("w?", queryItems.ToString().Remove(queryItems.Length - 1, 1));

                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                while (result.Read())
                {
                    var document = GetDocumentFromArray(hashListDocuments, result.GetInt32(0));
                    document.MinIndex = result.GetInt32(1);//"indice"
                }

                result.Close();
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                throw ex;
            }
        }

        /// <summary>
        /// Obtiene un documento de un array.
        /// </summary>
        /// <param name="documents"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private static DocumentoEntity GetDocumentFromArray(DocumentoEntity[] documents, int id)
        {
            for (var i = 0; i < documents.Length; i++)
            {
                if (documents[i].Id == id)
                {
                    return documents[i];
                }
            }

            return new DocumentoEntity(id);
        }

        /// <summary>
        /// Persiste el documento y retorna el id.
        /// </summary>
        /// <param name="documento"></param>
        /// <returns></returns>
        public static int SaveDocument(DocumentoEntity documento)
        {
            var connection = DbConnection.GetDbInstance().GetDbConnection();
            var myCommand = new SqlCommand();

            try
            {
                myCommand.Connection = connection;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = InsertDocument;

                var paramId = new SqlParameter("@idDocument", SqlDbType.Int);
                paramId.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(paramId);

                myCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = documento.Name;
                myCommand.Parameters.Add("@location", SqlDbType.NVarChar).Value = documento.Location;
                myCommand.Parameters.Add("@pagesCount", SqlDbType.Int).Value = documento.PagesCount;
                myCommand.Parameters.Add("@maxTF", SqlDbType.Int).Value = documento.MaxTf;
                myCommand.Parameters.Add("@size", SqlDbType.Int).Value = documento.Size;

                myCommand.ExecuteNonQuery();
                documento.Id = (int)myCommand.Parameters["@idDocument"].Value;

                return documento.Id;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());

                return 0;
            }
        }

        //TODO: esto deberia ir en el objeto de BD
        //public int Save(DocumentoEntity doc) {
        //    idTermino = TermDB.saveTerm(doc, this);
        //    return idTermino;
        //}
        #endregion
    }
}